const { getConn } = require('../service/database.mysql')
const { selectWhere } = require('../utils/sqlUtils')
const Base = {}

Base.pageSize = 10

Base.fetchAll = async (table, where) => {
  if (!where) {
    where = ''
  }
  return await Base.fetchWhere(table, where)
}

Base.fetchOne = async (table, where) => {
  let res = await Base.fetchWhere(table, where, 'limit 1')
  if (res.length > 0) {
    return res[0]
  } else {
    return null
  }
}

Base.fetchPage = async (table, where, page, pageSize) => {
  if (!pageSize) {
    pageSize = Base.pageSize
  }
  const limitSql = 'limit ' + pageSize + ' offset ' + (page - 1) * pageSize
  return await Base.fetchWhere(table, where, limitSql)
}

Base.fetchWhere = async (
  table,
  where,
  limit = '',
  order = 'order by id desc'
) => {
  try {
    let sql = selectWhere(table, where, limit, order)
    const conn = await getConn()
    const objs = await conn.query(sql)
    conn.release()
    return objs
  } catch (error) {
    console.log(error)
    return []
  }
}

Base.countWhere = async (table, where) => {
  try {
    const conn = await getConn()
    const sql = 'SELECT count(*) as count from ' + table + ' ' + where
    const res = await conn.query(sql)
    conn.release()
    return res[0].count
  } catch (err) {
    return -1
  }
}

Base.query = async (sql) => {
  const conn = await getConn()
  try {
    return await conn.query(sql)
  } catch (err) {
    return null
  } finally {
    conn.release()
  }
}

Base.save = async (table, obj) => {
  try {
    const conn = await getConn()
    if (!obj.id) {
      const res = await conn.query('INSERT INTO ' + table + ' SET ?', obj)
      conn.release()
      return res.insertId
    } else {
      const res = await conn.query('UPDATE ' + table + ' SET ? WHERE Id = ?', [
        obj,
        obj.id,
      ])
      conn.release()
      if (res.affectedRows + res.changedRows > 0) {
        return obj.id
      }
      return 0
    }
  } catch (error) {
    console.log('save', error)
    return 0
  }
}

// params : [{table,obj},{table,obj}]
Base.saveTx = async (...params) => {
  let err = null
  try {
    const conn = await getConn()
    let res = await conn.beginTransaction()
    console.log('saveTx', res)
    try {
      for (let param of params) {
        let { table, obj } = param
        if (obj.id <= 0) {
          let res = await conn.query('INSERT INTO ' + table + ' SET ?', obj)
          obj.id = res.insertId
          param.res = res
        } else {
          let res = await conn.query(
            'UPDATE ' + table + ' SET ? WHERE Id = ?',
            [obj, obj.id]
          )
          param.res = res
        }
      }
      await conn.commit()
    } catch (error) {
      await conn.rollback()
      err = error
    } finally {
      conn.release()
    }
  } catch (error) {
    err = error
  }
  return err
}

Base.delete = async (table, id) => {
  const conn = await getConn()
  try {
    const res = await conn.query('DELETE FROM ' + table + ' WHERE id = ?', id)
    return res.affectedRows
  } catch (error) {
    console.log(error)
    return 0
  } finally {
    conn.release()
  }
}

module.exports = Base
